﻿using PPSMS_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PPSMS_DAL;

public class StationStatistics_DAL : ICRUD_DAL<StationStatistics, long>
{
    /// <summary>
    /// 删除指定ID的记录
    /// </summary>
    /// <param name="ids">电站数据统计表的ID数组</param>
    public void DeleteByIds(params long[] ids)
    {
        string sql = $"delete from STATION_STATISTICS where Id in ({string.Join(",", ids)})";
        DBHelper.ExecuteNonQuery(sql);
    }

    /// <summary>
    /// 获取当前最大ID值
    /// </summary>
    /// <returns>ID</returns>
    public long GetCurrentValue()
    {
        string sql = "select rowid from STATION_STATISTICS";
        return DBHelper.ExecuteQuery(sql, DBHelper.ExecuteScalarToInt, new object[0]);
    }


    /// <summary>
    /// 新增记录
    /// </summary>
    /// <param name="t">电站数据统计表对象</param>
    public void Insert(StationStatistics t)
    {
        string sql = "insert into STATION_STATISTICS (DATE,TOTAL_ENERGY_PRODUCED,TOTAL_REVENUE,CO2_REDUCTION) values(?,?,?,?)";
        DBHelper.ExecuteNonQuery(sql, t.Date, t.TotalEnergyProduced, t.TotalRevenue, t.Co2Reduction);
    }

    /// <summary>
    /// 将一个 DataRow 对象转换为对应的 StationStatistics 对象
    /// </summary>
    /// <param name="dr"></param>
    /// <returns>电站数据统计表对象</returns>
    public StationStatistics ResultHandler(DataRow dr)
    {
        return new StationStatistics()
        {
            Date = dr.Field<string>("DATE"),
            TotalEnergyProduced = dr.Field<double>("TOTAL_ENERGY_PRODUCED"),
            TotalRevenue = dr.Field<double>("TOTAL_REVENUE"),
            Co2Reduction = dr.Field<double>("CO2_REDUCTION")
        };
    }

    /// <summary>
    /// 将一个 DataTable 对象转换为对应的 List<StationStatistics> 对象
    /// </summary>
    /// <param name="dr"></param>
    /// <returns>一个包含所有电站数据统计表信息的List</returns>
    public List<StationStatistics> ResultHandlerList(DataTable dr)
    {
        List<StationStatistics> list = new List<StationStatistics>();
        foreach (DataRow item in dr.Rows)
        {
            list.Add(ResultHandler(item));
        }
        return list;
    }

    /// <summary>
    /// 获取所有电站数据统计表的记录
    /// </summary>
    /// <returns>一个包含所有电站数据统计表信息的List</returns>
    public List<StationStatistics> SelectAll()
    {
        string sql = "select * from STATION_STATISTICS";
        return DBHelper.ExecuteQuery(sql, ResultHandler, new object[0]);
    }

    public List<StationStatistics> SelectAllByDate(string Date)
    {
        string sql1 = $"select * from STATION_STATISTICS where Date like '____-12-31 23%' ORDER BY Date ASC";
        string sql2 = $"select * from STATION_STATISTICS where Date like '{Date}%' ORDER BY Date ASC";
        if (Date != "ALL")
        {
            sql1 = sql2;
        }
        return DBHelper.ExecuteQuery(sql1, ResultHandler, new object[0]);
    }


    /// <summary>
    /// 根据ID获取电站数据统计表的记录
    /// </summary>
    /// <param name="id"></param>
    /// <returns>电站数据统计表对象</returns>
    public StationStatistics? SelectById(long id)
    {
        return SelectAll().FirstOrDefault(x => x.ID == id);
    }

    /// <summary>
    /// 根据条件修改电站数据统计表的记录
    /// </summary>
    /// <param name="t">新的电站数据统计表对象</param>
    /// <param name="id">旧的ID</param>
    /// <exception cref="ArgumentException"></exception>
    public void UpdateById(StationStatistics t, long id)
    {
        string sql = "updata STATION_STATISTICS set ";
        var list = new List<object>();
        if (t.Date != null)
        {
            sql += "DATE = ?,";
            list.Add(t.Date);
        }
        if (t.TotalEnergyProduced != null)
        {
            sql += "TOTAL_ENERGY_PRODUCED = ?,";
            list.Add(t.TotalEnergyProduced);
        }
        if (t.TotalRevenue != null)
        {
            sql += "TOTAL_REVENUE = ?,";
            list.Add(t.TotalRevenue);
        }
        if (t.Co2Reduction != null)
        {
            sql += "CO2_REDUCTION = ?,";
            list.Add(t.Co2Reduction);
        }
        sql = sql.TrimEnd(',');
        if (list.Count == 0) throw new ArgumentException("没有任何属性需要更新");
        sql += " where ID = ?";
        list.Add(id);
        DBHelper.ExecuteNonQuery(sql, list.ToArray());
    }

    public DataTable SelectByDateDAL(string date, string table)
    {
        string sql = $"select *from {table} where DATE like '{date}%' order by DATE asc";
        return DBHelper.ExecuteQuery(sql, ResultHandlerDataTable, new object[0]);
    }

    public DataTable SelectByDatesDAL(string date, string table)
    {
        string sql = "";
        if (table == "STATION_STATISTICS")
        {
            sql = $"select {date} AS DATE,SUM(TOTAL_ENERGY_PRODUCED) as TOTAL_ENERGY_PRODUCED,SUM(TOTAL_REVENUE) as TOTAL_REVENUE,SUM(CO2_REDUCTION) as CO2_REDUCTION from STATION_STATISTICS WHERE  DATE LIKE {date}||'%' GROUP BY {date} order by {date} asc";
        }
        else
        {
            sql = $"select INVERTERID,{date} AS DATE,SUM(DAILY_ENERGY) as DAILY_ENERGY,SUM(CUMULATIVE_ENERGY) as CUMULATIVE_ENERGY,SUM(CO2_REDUCTION) as CO2_REDUCTION from INVERTER_STATISTICS WHERE  DATE LIKE {date}||'%' GROUP BY {date} ,INVERTERID order by {date} asc";
        }
        return DBHelper.ExecuteQuery(sql, ResultHandlerDataTable, new object[0]);
    }
    public DataTable ResultHandlerDataTable(DataTable dr)
    {
        return dr;
    }

}
